<?php
class Search_Model_ChinryomadoriMatrix extends Apm_Model_Base{

	/**
	 * 賃料×間取りマトリクスデータ取得
	 *
	 * 賃料×間取りマトリクスデータの取得を行う
	 * @access	public
	 * @param	array
	 * @return	array マトリクスデータ
	 */
	public function getMatrix($params)
	{
		$sql = "";
		$db_params = array();
		
		// 検索タイプで分岐
		if(array_key_exists("SrhType", $params)){
			// 住所検索の場合
			if($params["SrhType"] == Apm_Config_Search::SRHTYPE_JYUSYO)
			{
				$sql = $this->generateSikuchosonSql($params, $db_params);
			}
			// 沿線・駅検索の場合
			elseif($params["SrhType"] == Apm_Config_Search::SRHTYPE_ENSENEKI)
			{
				$sql = $this->generateEkiSql($params, $db_params);
			}
		}
		if (!empty($sql))
		{
			return $this->dbGetList($sql, $db_params);
		}
		return array();
	}
	
	/**
	 * 賃料×間取りラベルデータ取得
	 *
	 * 賃料×間取りラベルデータの取得を行う
	 * @access	public
	 * @param	array
	 * @return	array ラベルデータ
	 */
	public function getLabel($params)
	{
		$sql = "";
		$db_params = array();
		
		// 検索タイプで分岐
		if(array_key_exists("SrhType", $params)){
			// 住所検索の場合
			if($params["SrhType"] == Apm_Config_Search::SRHTYPE_JYUSYO)
			{
				$sql = $this->generateSikuchosonLabelSql($params, $db_params);
			}
			// 沿線・駅検索の場合
			elseif($params["SrhType"] == Apm_Config_Search::SRHTYPE_ENSENEKI)
			{
				$sql = $this->generateEkiLabelSql($params, $db_params);
			}
		}
		if (!empty($sql))
		{
			return $this->dbGetData($sql, $db_params);
		}
		return array();
	}
	
	/**
	 * 賃料×間取りマトリクスデータを表示用に変換
	 *
	 * 賃料×間取りマトリクスデータを表示用に変換を行う
	 * @access	public
	 * @param	array
	 * @return	array マトリクスデータ
	 */
	public function convertMatrixData($result, $params, &$data)
	{
		$data['matrix'] = array();
		$cnt = 0;
		
		// ガラ作成
		$tinryo_from = 0;
		$tinryo_to   = 9999999;
		//  賃料体系
		$tinryo_arr = array(0, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000, 100000
							, 110000, 120000, 130000, 140000, 150000, 200000, 300000, 500000, 9999999);
		$tinryo_cnt = count($tinryo_arr);
		//  間取り体系
		$madori_cnt = 8;
		//  賃料下限
		if (isset($params['tinryo1']))
		{
			$tinryo_from = intval($params['tinryo1']);
			if ($tinryo_from < 150000) {
				$tinryo_from = intval($tinryo_from);
			} else if ($tinryo_from < 200000) {
				$tinryo_from = 150000;
			} else if ($tinryo_from < 300000) {
				$tinryo_from = 200000;
			} else if ($tinryo_from < 500000) {
				$tinryo_from = 300000;
			} else if ($tinryo_from < 1000000) {
				$tinryo_from = 500000;
			} else {
				$tinryo_from = 9999999;
			}
		}
		// 賃料上限
		if (isset($params['tinryo2']))
		{
			$tinryo_to = intval($params['tinryo2']);
			if ($tinryo_to < 150000) {
				$tinryo_to = intval($tinryo_to);
			} else if ($tinryo_to < 200000) {
				$tinryo_to = 150000;
			} else if ($tinryo_to < 300000) {
				$tinryo_to = 200000;
			} else if ($tinryo_to < 500000) {
				$tinryo_to = 300000;
			} else if ($tinryo_to < 1000000) {
				$tinryo_to = 500000;
			} else {
				$tinryo_to = 9999999;
			}
		}
		// 賃料ループ
		for ($i=0; $i<$tinryo_cnt; $i++)
		{
			if ($tinryo_from <= $tinryo_arr[$i] && $tinryo_arr[$i] <= $tinryo_to)
			{
				// 賃料セット
				$data['matrix'][$i]['CINRYO'] = $tinryo_arr[$i];
				
				// 間取りループ
				for ($j=0; $j<$madori_cnt; $j++)
				{
					// 間取りセット
					$data['matrix'][$i]['BKN_DATA'][$j]['BKN_CNT'] = 0;
				}
			}
		}
		
		// 結果ループ
		foreach ($result as $matrix)
		{
			// 間取り取得
			$j = $this->getMadoriKbn($matrix['T03_MADORI_CD']);
			if ($j == "") {
				continue;
			}
			
			// 賃料取得
			if ((string)$matrix['T03_YACHIN'] == (string)(int)$matrix['T03_YACHIN']) {
				// 切りの良い数値
				$i = array_search($matrix['T03_YACHIN'], $tinryo_arr);
				$data['matrix'][$i]['BKN_DATA'][$j]['BKN_CNT'] += intval($matrix['BUKEN_COUNT']);
				
				if ($matrix['T03_YACHIN'] != 0 && $matrix['T03_YACHIN'] != 9999999) {
					// 両端以外の場合、下位層にも反映される
					$data['matrix'][$i-1]['BKN_DATA'][$j]['BKN_CNT'] += intval($matrix['BUKEN_COUNT']);
				}
				
			} else {
				// 端数あり
				$i = array_search(floor($matrix['T03_YACHIN']), $tinryo_arr);
				$data['matrix'][$i]['BKN_DATA'][$j]['BKN_CNT'] += intval($matrix['BUKEN_COUNT']);
				
			}
			//$i = array_search($matrix['T03_YACHIN'], $tinryo_arr);
			
			// 物件件数加算
			//$data['matrix'][$i]['BKN_DATA'][$j]['BKN_CNT'] += intval($matrix['BUKEN_COUNT']);
			
			// 件数合計
			$cnt += intval($matrix['BUKEN_COUNT']);
		}
		
		// 合計数
		$data['TOTAL_CNT'] = number_format($cnt);
	}
	
	/**
	 * 間取りコードをマトリクス区分に変換
	 *
	 * @access	public
	 * @param	string
	 * @return	string マトリクス区分
	 */
	public function getMadoriKbn($madori_cd)
	{
		$kbn = "";
		
		switch ($madori_cd)
		{
			case Apm_Config_Search::MADORI_1R:
				$kbn = "0";
				break;
			case Apm_Config_Search::MADORI_1K:
			case Apm_Config_Search::MADORI_1DK:
			case Apm_Config_Search::MADORI_1SDK:
				$kbn = "1";
				break;
			case Apm_Config_Search::MADORI_1LDK:
			case Apm_Config_Search::MADORI_1SLDK:
				$kbn = "2";
				break;
			case Apm_Config_Search::MADORI_2R:
			case Apm_Config_Search::MADORI_2K:
			case Apm_Config_Search::MADORI_2DK:
			case Apm_Config_Search::MADORI_2SDK:
				$kbn = "3";
				break;
			case Apm_Config_Search::MADORI_2LDK:
			case Apm_Config_Search::MADORI_2SLDK:
				$kbn = "4";
				break;
			case Apm_Config_Search::MADORI_3R:
			case Apm_Config_Search::MADORI_3K:
			case Apm_Config_Search::MADORI_3DK:
			case Apm_Config_Search::MADORI_3SDK:
				$kbn = "5";
				break;
			case Apm_Config_Search::MADORI_3LDK:
			case Apm_Config_Search::MADORI_3SLDK:
				$kbn = "6";
				break;
			default:
				if ($madori_cd >= '40' and $madori_cd < '80') {
					$kbn = "7";
				}
				break;
		}
		
		return $kbn;
	}
	
	/**
	 * 賃料×間取りマトリクス取得SQL文の取得（市区町村）
	 *
	 * 賃料×間取りマトリクス取得SQL文の取得を行う
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 賃料×間取りマトリクス取得SQL
	 */
	public function generateSikuchosonSql($params, &$db_params)
	{
		// インスタンス生成
		$loader = new Zend_Application_Module_Autoloader(array(
			'namespace' => 'Rstobi',
			'basePath' => APPLICATION_PATH . '/modules/rstobi',
		));
		$this->_model_rstobi_common = new Rstobi_Model_Common();
		$this->_model_jyusyosearch = new Rstobi_Model_JyusyoSearch();
		
		$sql = "";
		// マトリクス用SELECT句を取得
		$sql .= $this->getSelectSql();
		
		// OEMコードを判定しFROM句とWHERE句を取得
		$oemSql = $this->_model_rstobi_common->getOemFromWhere($params, &$db_params);
		
		// FROM句を取得
		$sql .= $this->_model_jyusyosearch->getFromForJyusyosearch();
		$sql .= $oemSql['from'];
		
		/*** WHERE句を取得 【ここから】***/
		// 住所での絞込み
		$sql .= $this->_model_jyusyosearch->getWhereForJyusyosearch($params, &$db_params);
		// OEMでの絞込み
		$sql .= $oemSql['where'];
		// 基本条件での絞込み
		$sql .= $this->_model_rstobi_common->getKihonWhere($params, &$db_params);
		// こだわり条件での絞込み
		$sql .= $this->_model_rstobi_common->getKodawariWhere($params, &$db_params);
		// 部屋名寄せ
		$sql .= ') T03 ';
		//$sql .= ' where';
		//$sql .= ' T03_HEYA_ROW = 1)';
		/*** WHERE句を取得 【ここまで】***/
		
		// マトリクス用GROUP BY/ORDER BY句を取得
		$sql .= $this->getGroupOrderSql();
		
    	return $sql;
	}
	
	/**
	 * 賃料×間取りマトリクス取得SQL文の取得（沿線・駅）
	 *
	 * 賃料×間取りマトリクス取得SQL文の取得を行う
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 賃料×間取りマトリクス取得SQL
	 */
	public function generateEkiSql($params, &$db_params)
	{
		// インスタンス生成
		$loader = new Zend_Application_Module_Autoloader(array(
			'namespace' => 'Rstobi',
			'basePath' => APPLICATION_PATH . '/modules/rstobi',
		));
		$this->_model_rstobi_common = new Rstobi_Model_Common();
		$this->_model_ekisearch = new Rstobi_Model_EnsenekiSearch();
		
		$sql = "";
		// マトリクス用SELECT句を取得
		$sql .= $this->getSelectSql();
		
		// OEMコードを判定しFROM句とWHERE句を取得
		$oemSql = $this->_model_rstobi_common->getOemFromWhere($params, &$db_params);
		
		// FROM句を取得
		$sql .= $this->_model_ekisearch->getFromForEkisearch($params, &$db_params);
		$sql .= $oemSql['from'];
		
		/*** WHERE句を取得 【ここから】***/
		// 沿線・駅での絞込み
		$sql .= $this->_model_ekisearch->getWhereForEkisearch($params, &$db_params);
		// OEMでの絞込み
		$sql .= $oemSql['where'];
		// 基本条件での絞込み
		$sql .= $this->_model_rstobi_common->getKihonWhere($params, &$db_params);
		// こだわり条件での絞込み
		$sql .= $this->_model_rstobi_common->getKodawariWhere($params, &$db_params);
		// 部屋名寄せ
		$sql .= ') T03 ';
		//$sql .= ' where';
		//$sql .= ' T03_HEYA_ROW = 1)';
		/*** WHERE句を取得 【ここまで】***/
		
		// マトリクス用GROUP BY/ORDER BY句を取得
		$sql .= $this->getGroupOrderSql();
		
    	return $sql;
	}
	
	/**
	 * 賃料×間取りラベル取得SQL文の取得（市区町村検索）
	 *
	 * 賃料×間取りラベル取得SQL文の取得を行う
	 * @access	public
	 * @param	string SQL
	 * @return	string 賃料×間取りラベル取得SQL
	 */
	public function generateSikuchosonLabelSql($params, &$db_params)
	{
		// パラメータ
		$db_params['strCityCd'] = $params['CityCd'][0];
		
		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M01_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M01_SHIKUCHOUSON_CD as SHIKUCHOUSON_CD ";
		$sql .= "    ,M01_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M01_SHIKUCHOUSON as SHIKUCHOUSON ";
		$sql .= "FROM ";
		$sql .= "     M01JYUUSHO ";
		$sql .= "WHERE ";
		$sql .= "        M01_TODOUFUKEN_CD = substr(:strCityCd, 1, 2) ";
		$sql .= "    AND M01_SHIKUCHOUSON_CD = substr(:strCityCd, 3, 3) ";
		$sql .= "GROUP BY ";
		$sql .= "     M01_TODOUFUKEN_CD ";
		$sql .= "    ,M01_SHIKUCHOUSON_CD ";
		$sql .= "    ,M01_TODOUFUKEN ";
		$sql .= "    ,M01_SHIKUCHOUSON ";
		
		return $sql;
	}
	
	/**
	 * 賃料×間取りラベル取得SQL文の取得（沿線・駅検索）
	 *
	 * 賃料×間取りラベル取得SQL文の取得を行う
	 * @access	public
	 * @param	string SQL
	 * @return	string 賃料×間取りラベル取得SQL
	 */
	public function generateEkiLabelSql($params, &$db_params)
	{
		// パラメータ
		$db_params['strPrefCd'] = $params['PrefCd'][0];
		$db_params['strStCd']   = $params['StCd'][0];
		
		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "    ,M03.M03_ENSEN_CD as ENSEN_CD ";
		$sql .= "    ,M03.M03_ENSEN_NAME as ENSEN_NAME ";
		$sql .= "    ,M03.M03_EKI_CD as EKI_CD ";
		$sql .= "    ,M03.M03_EKI_NAME as EKI_NAME ";
		$sql .= "FROM ";
		$sql .= "     M03EKI M03 ";
		$sql .= "    ,M43TODOUFUKEN M43 ";
		$sql .= "WHERE ";
		$sql .= "        M03.M03_EKI_CD = :strStCd ";
		$sql .= "    AND M03.M03_TODOUFUKEN_CD = :strPrefCd ";
		$sql .= "    AND M03.M03_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		
		return $sql;
	}
	
	/**
	 * 賃料×間取りマトリクス取得SQL文の取得（SELECT句）
	 *
	 * 賃料×間取りマトリクス取得SQL文の取得を行う
	 * @access	public
	 * @param	string SQL
	 * @return	string 賃料×間取りマトリクス取得SQL
	 */
	public function getSelectSql()
	{
		// ｸｴﾘ文字列（Select句）
		$strQrySelect = '';

$strQrySelect = <<< DOC_END
SELECT 
     T03_YACHIN
    ,T03_MADORI_CD
    ,count(*) AS BUKEN_COUNT
FROM (SELECT 
         T03.T03_HEYA_CD
        ,CASE
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 20000 THEN 0
            WHEN ((T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 150000
                  AND (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) = trunc((T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)),-4)) THEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0))
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 150000 THEN trunc((T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)),-4)+0.5
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) = 150000 THEN 150000
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 200000 THEN 150000.5
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) = 200000 THEN 200000
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 300000 THEN 200000.5
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) = 300000 THEN 300000
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 500000 THEN 300000.5
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) = 500000 THEN 500000
            WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 1000000 THEN 500000.5
            ELSE 9999999
         END as T03_YACHIN
        ,T03.T03_MADORI_CD
DOC_END;

/*
$strQrySelect = <<< DOC_END
SELECT 
     T03_YACHIN
    ,T03_MADORI_CD
    ,count(*) AS BUKEN_COUNT
FROM (SELECT *
    FROM (SELECT ROW_NUMBER() OVER (partition by T03.T03_ORIGINAL_TATEMONO_CD01,
                T03.T03_ORIGINAL_HEYA_CD01, T03_KASHIDASHI_KUBUN
            ORDER BY T03.T03_TOUROKUBI DESC) as T03_HEYA_ROW
            ,T03.T03_HEYA_CD
            ,CASE
                WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 20000 THEN 0
                WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 150000 THEN trunc((T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)),-4)
                WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 200000 THEN 150000
                WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 300000 THEN 200000
                WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 500000 THEN 300000
                WHEN (T03.T03_YACHIN + NVL(T03.T03_YACHIN_TAX,0)) < 1000000 THEN 500000
                ELSE 9999999
             END as T03_YACHIN
            ,T03.T03_MADORI_CD
DOC_END;
*/
		return $strQrySelect;
	}
	
	/**
	 * 賃料×間取りマトリクス取得SQL文の取得（GROUP BY句・ORDER BY句）
	 *
	 * 賃料×間取りマトリクス取得SQL文の取得を行う
	 * @access	public
	 * @param	string SQL
	 * @return	string 賃料×間取りマトリクス取得SQL
	 */
	public function getGroupOrderSql()
	{
		// ｸｴﾘ文字列（Select句）
		$strQrySelect = '';

$strQrySelect = <<< DOC_END
GROUP BY
     T03_YACHIN
    ,T03_MADORI_CD
ORDER BY
     T03_YACHIN
    ,T03_MADORI_CD
DOC_END;

		return $strQrySelect;
	}
	
}
?>